/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package cn.jgls.bi.dataprocess;
import java.util.List;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.RowMapperResultReader;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Iterator;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;

/**
 *
 * @author Administrator
 */
public class biStockJdbcDao extends JdbcDaoSupport implements biStockDataDao {
    
    @Override
    public void biStockData(Date adTsnDate, int aiShopId, String asShopBm,
        int aiDateNo, String asYear, int aiYybh, int aiMan_id, String asTname) {
        int iShopCnt = 0;
        int iRc = 0;
        int aiYear = 0, aiWeek = 0;
        int i = 0;
        final int isize;
        final int isize2;
        final int iyear,  iweek;
        final Date dd;
        double dAmt=0;
        double dValue=0;
        int isup_id=0;
        String sql = "";
        String sLb = "";
        String sDate = "";
        int iYybh = 0;
        ArrayList<String> alLb = new ArrayList<String>();
        ArrayList<Integer> aiSC = new ArrayList<Integer>();
        ArrayList<Double> adAmt = new ArrayList<Double>();
        ArrayList<Double> adValue = new ArrayList<Double>();

        final ArrayList<String> alLb2 = new ArrayList<String>();
        final ArrayList<Integer> aiSC2 = new ArrayList<Integer>();
        final ArrayList<Double> adAmt2 = new ArrayList<Double>();
        final ArrayList<Double> adValue2 = new ArrayList<Double>();

        List lstbiStock;
        List lstbiStockYyb;
        
        SimpleDateFormat ddd = new SimpleDateFormat("yyyy-MM-dd");
        java.util.Calendar rightNow = Calendar.getInstance();
        rightNow.add(Calendar.DATE, -1);
        String lsYesterday = ddd.format(rightNow.getTime());
        java.sql.Date ldYesterday = java.sql.Date.valueOf(lsYesterday);

        if (adTsnDate.before(ldYesterday)) {
            //汇总表
            asTname = "dvlpr_asm.t_spsjk_old ";
        } else {
            //工作表，只存储昨天和前天的数据
            asTname = "dvlpr_asm.t_spsjk ";
            //sTname = " t_waste_book ";
        }
        
        //该程序只运行一次即可，所以目前定为2店，如2店闭店，请换为其他门店
        if (aiShopId != 2) {
            return;
        }else{

            sDate = new SimpleDateFormat("w").format(adTsnDate);
            aiWeek = Integer.parseInt(sDate);
            //cal.set(iRc, iRc, iRc);

            System.out.println("now processing    biStock's Data... Date:" + adTsnDate +
                    " shopid;" + aiShopId + " please wait...");
            aiYear = Integer.parseInt(asYear);
            //取总销售金额
            JdbcTemplate jt = getJdbcTemplate();
            sql = "select a.lb,round(nvl(sum(a.sl),0),2) as stock_amt,round(nvl(sum(a.jj * a.sl),0),2) as stock_value,b.sup_id  from "+asTname+" a , sy_spk b where a.lb=b.lb and spkdate=? and sl<>0 and a.lb is not null group by a.lb,b.sup_id";
            Object[] params = new Object[]{adTsnDate};
            int[] types = {Types.DATE};
            lstbiStock = jt.query(sql, params, types, new RowMapperResultReader(new biStockRM()));
            Iterator Iter = lstbiStock.iterator();
            biStock ltam = new biStock();
            while (Iter.hasNext()) {
                ltam    = (biStock) Iter.next();
                sLb     = ltam.getLb();
                isup_id = ltam.getSup_id();
                dAmt    = ltam.getStock_amt();
                dValue  = ltam.getStock_value();

                alLb.add(sLb);
                aiSC.add(isup_id);
                adAmt.add(dAmt);
                adValue.add(dValue);
                //cls
                sLb = "";
                isup_id = 0;
                dAmt=0;
                dValue=0;
            }

            //begin insert into biStock
            //1:delete
            sql = "delete from t_bi_stock where tsn_date = ? ";
            iRc = jt.update(sql, params, types);
            //2:insert

            iyear = aiYear;
            iweek = aiWeek;
            dd = adTsnDate;
            //总的
            isize = alLb.size();
            for (i = 0; i < isize; i++) {
                alLb2.add(alLb.get(i));
                aiSC2.add(aiSC.get(i));
                adAmt2.add(adAmt.get(i));
                adValue2.add(adValue.get(i));
            }
            if (isize > 0) {
                sql = "insert into t_bi_stock " +
                        "(tsn_year,tsn_week,tsn_date,lb,stock_amt,stock_value,sup_id)" +
                        " values(?,?,?,?,?,?,?)";
                BatchPreparedStatementSetter setter = null;
                setter = new BatchPreparedStatementSetter() {

                    @Override
                    public int getBatchSize() {
                        return isize;
                    }

                    public void setValues(PreparedStatement ps, int index) throws SQLException {
                        ps.setInt(1, iyear);
                        ps.setInt(2, iweek);
                        ps.setDate(3, dd);
                        ps.setString(4, alLb2.get(index));
                        ps.setDouble(5, adAmt2.get(index));
                        ps.setDouble(6, adValue2.get(index));
                        ps.setInt(7, aiSC2.get(index));
                    }
                };
                jt.batchUpdate(sql, setter);
            }
        }
   }

    class biStockRM implements RowMapper {

        public Object mapRow(ResultSet rs, int index)
                throws SQLException {
            biStock lmx = new biStock();
            lmx.setLb(rs.getString("lb").trim());
            lmx.setStock_amt(rs.getDouble("stock_amt"));
            lmx.setStock_value(rs.getDouble("stock_value"));
            lmx.setSup_id(rs.getInt("sup_id"));
            return lmx;
        }
    }

  
    
}
